﻿----INSERT TBL_KPI_INFO
----select KPI_GROUP, KPI_NAME, KPI_UNIT, KPI_WEIGHT, 'PMBO', 4,[ORDER] from tbl_kpi_info 
----WHERE KPI_ID IN(1,2,3,4,8,9)
----ORDER BY [ORDER]

--select * from TBL_KPI_INFO
----SELECT * FROM TBL_KPI_INFO WHERE LEVEL_STAFF = 'MBO'


----SELECT * FROM TBL_HR

--select * from TBL_BRANCH where BRANCH_NAME_SME like '%ho%'

--SELECT A.DAO,A.USER_NAME,B.* 
----INTO TBL_SALE_PERFORMANCE_KPI
--FROM TBL_HR A, TBL_KPI_INFO B
--WHERE A.group_id = B.GROUP_ID AND B.KPI_ID < 27
--ORDER BY A.DAO, B.[ORDER]


--SELECT * FROM TBL_KPI_INFO


--select * from TBL_SALE_PERFORMANCE_KPI

--delete from TBL_SALE_PERFORMANCE_KPI where DAO = 'khong co DAO'




--------GET DATA FROM TRANG DOAN
--SELECT * FROM TRANGDTT18_KPI_RAW where SALE_NAME = N'Nguyễn Hồng Ân'



declare @data_date_raw date = (select max(business_date) from TRANGDTT18.SME2017DAILY.[DBO].[FINAL_MSBO_KQ])

DELETE FROM TRANGDTT18_KPI_RAW

INSERT TRANGDTT18_KPI_RAW 
SELECT * 
FROM [TRANGDTT18].[SME2017DAILY].[DBO].[FINAL_MSBO_KQ] where business_date = @data_date_raw

delete from tbl_KPI_msbo_raw

insert tbl_KPI_msbo_raw
select * from TRANGDTT18_KPI_RAW 
------UNPIVOT KPI INDEX TO ROW
DELETE FROM TRANGDTT18_KPI_RAW_UNPIVOT


--SELECT * FROM TBL_KPI_MSBO_RAW WHERE BRANCH_CODE = 'VQN'

INSERT TRANGDTT18_KPI_RAW_UNPIVOT
  SELECT DAO,  VALUE_KPI, NAME_INDEX
  ,CASE WHEN NAME_INDEX LIKE 'AVGBAL_LOAN_%' THEN 19
		WHEN NAME_INDEX LIKE 'AVGBAL_DEPOSIT_%' THEN 20
		WHEN NAME_INDEX LIKE 'NEWNPL_LOAN_%' THEN 21
		WHEN NAME_INDEX LIKE 'AVGBAL_UPL_LOAN_%' THEN 24
		WHEN NAME_INDEX LIKE 'CUSTOMER_%' THEN 25
		WHEN NAME_INDEX LIKE 'CREDIT_CARD_%' THEN 26 END KPI_ID
	,CASE WHEN NAME_INDEX LIKE '%_TARGET' THEN 'TARGET'
		WHEN NAME_INDEX LIKE '%_ACT' THEN 'ACT'
		WHEN NAME_INDEX LIKE '%_REAL' THEN 'REAL' END CLASSIFY
		--INTO TRANGDTT18_KPI_RAW_UNPIVOT
  FROM 
   (SELECT  * 
   FROM TRANGDTT18_KPI_RAW) P
UNPIVOT
   (VALUE_KPI FOR NAME_INDEX IN 
      ([AVGBAL_LOAN_TARGET]      ,[AVGBAL_DEPOSIT_TARGET]      ,[NEWNPL_LOAN_TARGET]      ,[AVGBAL_UPL_LOAN_TARGET]      ,[CUSTOMER_TARGET]      ,[CREDIT_CARD_TARGET]      ,[AVGBAL_LOAN_ACT]      ,[AVGBAL_DEPOSIT_ACT]      ,[NEWNPL_LOAN_ACT]      ,[AVGBAL_UPL_LOAN_ACT]      ,[CUSTOMER_ACT]      ,[CREDIT_CARD_ACT]      ,[AVGBAL_LOAN_REAL]      ,[AVGBAL_DEPOSIT_REAL]      ,[NEWNPL_LOAN_REAL]      ,[AVGBAL_UPL_LOAN_REAL]      ,[CUSTOMER_REAL]      ,[CREDIT_CARD_REAL] )
)AS UNPVT;

------UPDATE RESULT KPI DAILY

--UPDATE A
--SET A.TARGET = B.TARGET, A.REAL = B.REAL, A.ACT = B.ACT
----SELECT *
--FROM TBL_SALE_PERFORMANCE_KPI A, (SELECT * 
----INTO TBL_CUSTOMER_CONTACT
-- FROM 
--(SELECT DAO, KPI_ID, CLASSIFY, VALUE_KPI
--FROM TRANGDTT18_KPI_RAW_UNPIVOT ) AS A
--PIVOT
--(MAX(VALUE_KPI) FOR CLASSIFY IN ([TARGET],[ACT],[REAL])) AS PVT) B
--WHERE A.DAO = B.DAO COLLATE DATABASE_DEFAULT AND A.KPI_ID = B.KPI_ID

delete from TBL_SALE_PERFORMANCE_KPI

insert TBL_SALE_PERFORMANCE_KPI
select a.DAO, a.USER_NAME, a.kpi_id,a.KPI_GROUP, a.KPI_NAME, a.KPI_UNIT, a.KPI_WEIGHT, a.LEVEL_STAFF, a.group_id, a.[ORDER], a.BRANCH_ID, a.REGION, isnull(b.TARGET,0), isnull(b.ACT,0),isnull(b.REAL,0), 0, 0 ,0, a.OBJECT_URL
from
(SELECT a.DAO, a.USER_NAME, c.KPI_ID, c.KPI_GROUP, c.KPI_NAME, c.KPI_UNIT, c.KPI_WEIGHT, c.LEVEL_STAFF, a.group_id, c.[ORDER], d.BRANCH_ID, d.REGION,  e.OBJECT_URL
FROM tbl_hr a, TBL_KPI_INFO c, TBL_BRANCH d, APPLICATION_OBJECT e where  a.BRANCH_CODE_SME = d.BRANCH_CODE_SME and c.OBJECT_ID = e.ID and a.group_id = c.GROUP_ID and a.group_id = 4 and c.LEVEL_STAFF = 'KMBO') A
left join (SELECT * 
--INTO TBL_CUSTOMER_CONTACT
 FROM 
(SELECT DAO, KPI_ID, CLASSIFY, VALUE_KPI
FROM TRANGDTT18_KPI_RAW_UNPIVOT ) AS A
PIVOT
(MAX(VALUE_KPI) FOR CLASSIFY IN ([TARGET],[ACT],[REAL])) AS PVT) B on a.DAO = b.Dao collate database_default and b.kpi_id= a.KPI_ID


--select * from TRANGDTT18_KPI_RAW_unpivot where dao = '5498'

delete from TBL_SALE_PERFORMANCE_KPI where USER_NAME is null


--select * from TBL_SALE_PERFORMANCE_KPI a, TBL_BRANCH b where a.BRANCH_ID = b.BRANCH_ID and b.BRANCH_NAME_SME like '%tan phu%'
--select * from  TRANGDTT18_KPI_RAW where dao = '14326'
----------UPDATE RAW DATA RELATED TO AVG BAL
DELETE FROM TBL_KPI_RAW_DNBQ_DAILY
declare @data_date date = (select max(business_date) from TRANGDTT18.SME2017DAILY.[DBO].[RAW_DU_NO_BQ])
------DƯ NỢ BÌNH QUÂN
INSERT TBL_KPI_RAW_DNBQ_DAILY
  SELECT  A.BUSINESS_DATE, BRANCH_CODE AS BRANCH_ID, APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, AVGBAL_QD, PRODUCT_GROUP, CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_DU_NO_BQ] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT and a.BUSINESS_DATE = @data_date


   ------HUY ĐỘNG BÌNH QUÂN
   	INSERT TBL_KPI_RAW_DNBQ_DAILY
  SELECT  A.BUSINESS_DATE, BRANCH_CODE AS BRANCH_ID, APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, AVGBAL_QD, PRODUCT_GROUP,'' AS CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_HUY_DONG_BQ] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT and a.BUSINESS_DATE = @data_date


   ----------NỢ XẤU 
   
   	INSERT TBL_KPI_RAW_DNBQ_DAILY
  SELECT A.BUSINESS_DATE, BRANCH_CODE AS BRANCH_ID,'LOANBADBANK' AS APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, BAL_QD, PRODUCT_GROUP, CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_NO_XAU] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT and a.BUSINESS_DATE = @data_date


   --------DƯ NỢ TÍN CHẤP BÌNH QUÂN 
   	INSERT TBL_KPI_RAW_DNBQ_DAILY
  SELECT A.BUSINESS_DATE, BRANCH_CODE AS BRANCH_ID,'LOANUNSECURED' AS APP, A.DAO, A.DAO_CIF, ACCTNO, A.CIF, AVGBAL_QD, PRODUCT_GROUP, CHUONG_TRINH_SP, ISSUE_DATE, B.CUS_NAME, B.DAO AS DAO_CUST, B.DAO_NAME , D.BRANCH_NAME_SME, D.REGION
  --INTO TBL_KPI_RAW_DNBQ_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_DU_NO_TIN_CHAP_BQ] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND A.BRANCH_CODE = D.BRANCH_ID COLLATE DATABASE_DEFAULT and a.BUSINESS_DATE = @data_date

   --------UPDATE RAW DATA CARD DAILY
   DELETE FROM TBL_KPI_RAW_CARD_DAILY


   INSERT TBL_KPI_RAW_CARD_DAILY
   SELECT A.BUSINESS_DATE	,CONTRACT_NUMBER	,CARD_NUMBER	,A.CIF	,CARD_STATUS	,CARD_DATE_OPEN	,CARD_TYPE	,UNLOCK_DATE	,A.DAO	,DAO_CIF	,CONTRACT_STATUS	,ACTUAL_DATE_EXPIRE	,NOTE	,B.CUS_NAME	,B.BRANCH_ID	,D.BRANCH_NAME_SME	,D.REGION	--INTO TBL_KPI_RAW_CARD_DAILY
	FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_THE] A, TBL_CUSTOMER B, TBL_BRANCH D
   WHERE A.CIF = B.CIF COLLATE DATABASE_DEFAULT AND B.BRANCH_ID = D.BRANCH_ID COLLATE DATABASE_DEFAULT and a.BUSINESS_DATE = @data_date


   --------UPDATE RAW DATA CUSTOMER ACTIVE DAILY

   DELETE FROM TBL_KPI_RAW_KHHD_DAILY

   INSERT TBL_KPI_RAW_KHHD_DAILY
    SELECT CONVERT(DATE, BUSINESS_DATE) AS BUSINESS_DATE
  ,DAO
  ,ISNULL( SO_LUY_KE,0) AS SO_LUY_KE
  ,ISNULL( DEC_2016,0) AS LAST_YEAR
  ,ISNULL( BAN_GIAO,0) AS BAN_GIAO
  ,ISNULL( NHAN_BAN_GIAO,0) AS NHAN_BAN_GIAO
  ,ISNULL( SO_KHHD_TANG_THEM,0) AS SO_KHHD_TANG_THEM
  --INTO TBL_KPI_RAW_KHHD_DAILY
   FROM TRANGDTT18.SME2017DAILY.[DBO].[RAW_KHHD_TANG_THEM]
   where BUSINESS_DATE = (select max(business_date) from TRANGDTT18.SME2017DAILY.[DBO].[RAW_KHHD_TANG_THEM])


   --select max(BUSINESS_DATE) as date_data, 'tbl_KPI_msbo_raw' from TRANGDTT18_KPI_RAW
update b
set b.date_data = a.date_data
from (select max(BUSINESS_DATE) as date_data from TRANGDTT18_KPI_RAW) a,  TBL_DATE_BUSINESS b
where b.name_table = 'tbl_KPI_msbo_raw'